<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 *
 *
 */
class Tourdates extends CI_Model
{
	/**
	 * Constructor
	 * @access protected
	 */
	function _construct()
	{
      // Call the Model constructor
      parent::_construct();
	}

	function tourTitle($tourId)
	{
		$tourId=(string)$tourId;
		
		$result = $this->db->query("SELECT tourTitle FROM tours WHERE id=$tourId");
		if ($result->num_rows() > 0)
		{
   			$row = $result->row();
   			$this->db->close();
			return $row->tourTitle;
		}
		$this->db->close();
		return false;
	}
	function listTours($tourId)
	{
		
		$result = $this->db->query("SELECT tourTitle,id FROM tours");
		$responce=array();
		$i=0;
		foreach($result->result() as $row)
		{
			$responce[$i]['name']=$row->tourTitle;
			$responce[$i]['id']=$row->id;
			if($tourId==$row->id)
			{
				$responce[$i]['selected']=1;
			}
			else
			{
				$responce[$i]['selected']=0;
			}
			$i++;
		}
		$this->db->close();
		return $responce;
	}
	function json($y="",$m="",$tourid="",$type="")
	{
		$n=($m!=2?(($m%2)^($m>7))+30:(!($y%400)||!($y%4)&&($y%25)?29:28));
		$dateS=$y."-".$m."-01";
		$dateE=$y."-".$m."-".$n;


		$SQL="select * from dates where tour_id=$tourid and date>='$dateS' and date<='$dateE' order by date asc";
		$result = $this->db->query($SQL);
		$i=0;
		$DATA=array();
		foreach ($result->result() as $row)
		{
    		$DATA[$i]->date=$row->date;
			$DATA[$i]->price_id=$row->price_id;
			$DATA[$i]->hotel_type=$row->type;

			$i++;
		}
		for($i=0;$i<count($DATA);$i++)
		{
			$SQL="select * from prices where id=".$DATA[$i]->price_id;
			$result = $this->db->query($SQL);
			foreach ($result->result() as $row)
			{
			//Load DBL	SNGL	TRPL	CHLD	HB
				$DATA[$i]->DBL=$row->DBL;
				$DATA[$i]->SNGL=$row->SNGL;
				$DATA[$i]->TRPL=$row->TRPL;
				$DATA[$i]->CHLD=$row->CHLD;
				$DATA[$i]->HB=$row->HB;
			}
		}
		$responce->page = 1;
		$responce->total = 1;
		$responce->records = 5;
		$rows[0]['cell']=array('1',"SNGL");
		$rows[1]['cell']=array('2',"DBL");
		$rows[2]['cell']=array('3',"TRPL");
		$rows[3]['cell']=array('4',"CHLD");
		$rows[4]['cell']=array('5',"HB");
		for($i=2;$i<=$n+1;$i++)
		{
			if($d=$this->search($i-1,$m,$y,$type,$DATA))
			{
				$rows[0]['cell'][$i]=$d->SNGL!=0?$d->SNGL:"";
				$rows[1]['cell'][$i]=$d->DBL!=0?$d->DBL:"";
				$rows[2]['cell'][$i]=$d->TRPL!=0?$d->TRPL:"";
				$rows[3]['cell'][$i]=$d->CHLD!=0?$d->CHLD:"";
				$rows[4]['cell'][$i]=$d->HB!=0?$d->HB:"";
			}
			else
			{
				$rows[0]['cell'][$i]="";
				$rows[1]['cell'][$i]="";
				$rows[2]['cell'][$i]="";
				$rows[3]['cell'][$i]="";
				$rows[4]['cell'][$i]="";
			}
		}
		$responce->rows=$rows;
		$this->db->close();
		return json_encode($responce);
	}

	function search($dd,$mm,$yyyy,$t,$DATA)
	{
		$date=$yyyy;
		if (strlen ($mm)<2) {
			$date.="-0".$mm;
		}
		else
		{
			$date.="-".$mm;
		}
		if (strlen ($dd)<2) {
			$date.="-0".$dd;
		}
		else
		{
			$date.="-".$dd;
		}
		for ($i=0;$i<count($DATA);$i++)
		{
			if(strcmp ($DATA[$i]->date,$date)==0&&$DATA[$i]->hotel_type==$t)//
			{
				return $DATA[$i];
			}
		}
		return false;
	}
	function clear($line,$year,$month,$tourId,$table)
	{
		
		$dS=$year."-".$month."-01";
		$dE=$year."-".($month+1)."-01";
		$query="Select price_id from dates where date>='$dS' and date<'$dE' and tour_id='$tourId' and type='$table' group by price_id";
		$result=$this->db->query($query);
		foreach($result->result() as $row)
		{
			$this->db->query("Update `prices` SET  `$line` =  0 WHERE  `id` =".$row->price_id);
		}
		$this->db->close();
	}
	function delete($year,$month,$tourId,$table)
	{
		
		$dS=$year."-".$month."-01";
		$dE=$year."-".($month+1)."-01";
		$query="Select price_id,id from dates where date>='$dS' and date<'$dE' and tour_id='$tourId' and type='$table' group by price_id";
		$result=$this->db->query($query);
		foreach($result->result() as $row)
		{
			$this->db->query("delete from  `prices`  WHERE  `id` =".$row->price_id);
			$this->db->query("delete from  `dates`  WHERE  `id` =".$row->id);
		}
		$this->db->close();
	}

	function edit($line,$days,$year,$month,$tourId,$table)
	{
	
		$date=$year."-".$month."-";

		foreach($days as $day=>$price)
		{
			$this->Log($day." = > ".$price);
			if($price!="")
			{
				$intDay=substr($day, 1);
				$dateTemplate=$date.$intDay;
				$query="Select price_id from dates where date='".$dateTemplate."' and tour_id='".$tourId."' and type='".$table."' group by price_id";
				
				$result=$this->db->query($query);
				if($result->num_rows()>0)
				{
				foreach($result->result() as $row)
					{
						$query="Select id from prices where `".$line."` =  ".$price." and `id` = ".$row->price_id;
					
						$rrr=$this->db->query($query);
						if ($rrr->num_rows()== 0)
						{
				   			$query="Update `prices` SET  `".$line."` =  ".$price." WHERE `id` = ".$row->price_id;
							$rrr=$this->db->query($query);
						
						}

					}
				}
				else
				{
					switch($line)
					{
						case "DBL":
							$DBL=$price;
							break;
						case "TRPL":
							$TRPL=$price;
							break;
						case "SNGL":
							$SNGL=$price;
							break;
						case "CHLD":
							$CHLD=$price;
							break;
						case "HB":
							$HB=$price;
							break;


					}
					$query="INSERT INTO `prices` (

											`DBL` ,
											`SNGL` ,
											`TRPL` ,
											`CHLD` ,
											`HB`
											)
											VALUES (
										  	'$DBL',
										  	'$SNGL',
											'$TRPL',
											'$CHLD',
											'$HB'
												);";
					
					$this->db->query($query);
					$query="select id from prices order by id desc limit 1";
					$result=$this->db->query($query);
					if ($result->num_rows() > 0)
					{
			   			$row = $result->row();
					}
					$id=$row->id;
					
					$query="INSERT INTO `dates` (
										`tour_id` ,
										`type` ,
										`date` ,
										`price_id`
										)
										VALUES (
										'".$tourId."',
										'".$table."',
										'".$dateTemplate."',
										'".$id."'
										);";
					$this->log($query);
					$this->db->query($query);
				}

			}
		}
		$this->db->close();

	}
	function parsexml($strxml,$tourId)
	{
	
		$this->parse_xml($this->get_work_sheet($strxml),$tourId);
	}
	function get_work_sheet($str)
	{
		$n=(int)strpos($str,"<Worksheet ");
		$e=(int)strpos($str,"Worksheet>");
		$str2=substr($str,$n,$e-$n+10);
		return $str2;
	}
	function short_to_date($str)
	{

		$str=str_replace(',','.' ,$str );
		if(preg_match("/^(\d|\d\d).(\d)/",$str))
		{

			$dd=explode('.',$str);
			if(strlen($dd[1])==1)
			{
				$dd[1]="0".$dd[1];
			}
			elseif (strlen($dd[1])>2)
			{
				$dd[1]=substr($dd[1],0,1);
			}
			if(strlen($dd[0])==1)
			{
				$dd[0]="0".$dd[0];
			}
			$test=(int)(Date('Y').$dd[1]);
			$test2=(int)Date('Ym');
			if($test2>$test)
			{
			$year=Date('Y')+1;
			}
			else
			{
				$year=Date('Y');
			}

			$str2= $year."-".(int)$dd[1]."-".(int)$dd[0];
		}
		else if(preg_match("/^(\d{4})-(\d|\d\d)-(\d|\d\d)T(\d)/",$str))  //2011-01-12T00:00:00.000
		{
			$dd=explode('-',$str);
			if(strlen($dd[1])==1)
			{
				$dd[1]="0".$dd[1];
			}
			$test=(int)($dd[0].$dd[1]);
			$test2=(int)Date('Ym');
			$this->log($test."  -  ".$test2);
			if($test2>$test)
			{
				$year=Date('Y')+1;
			}
			else
			{
				$year=Date('Y');
			}

			$str2= $year."-".(int)$dd[1]."-".(int)$dd[2];

		}
		if(preg_match("/^(\d{4})-(\d|\d\d)-(\d|\d\d)$/",$str2))
		{
			return $str2;
		}
		else
		{
			$this->Log("Vvedena nevernaya data tyra ".$str);
			return false;
			//die("Vvedena nevernaya data tyra");
		}
	}
	function parse_xml($xmlstr,$tour_id)
	{
 		$xmlstr=str_replace(':','' ,$xmlstr );
		$xml = new SimpleXMLElement($xmlstr);
		$hotelType="3";
		foreach ($xml->Table[0]->children()  as $key=>$value)
		{
			if($key=="Row")
			{
				$str="";
				foreach ($value->children()  as $key2=>$value2)
				{
					switch($value2->Data)
					{
						case "3*":
							$hotelType="3";
						break;
						case "3+/4*":
							$hotelType="4";
						break;
						case "4+/5*":
							$hotelType="5";
						break;
						default:
							$data[]=(string)$value2->Data;
						break;
					}
				}
				if($data[0]!='HOTEL CATEGORY'&&$data[1]!='D A T E S'&&$data[1]!=''&&$data[2]!='P.P. in DBL/BB')
				{
					$dates=$data[0];
					$DBL=$data[1];
					$SNGL=$data[2];
					$TRPL=$data[3];
					$CHLD=$data[4];
					$HB=$data[5];
					$aDates=explode(';',$dates);
					for($i=0;$i<count($aDates);$i++)
					{
						$aDates[$i]=$this->short_to_date(trim($aDates[$i]));

						//Create line in Prices table and get id
						$query= "INSERT INTO  `prices` (
												`DBL` ,
												`SNGL` ,
												`TRPL` ,
												`CHLD` ,
												`HB`
												)
												VALUES (
	  											'$DBL',
												'$SNGL',
												'$TRPL',
												'$CHLD',
												'$HB'
												);";
						$this->db->query($query);
						$query="Select id from prices order by id desc";
						$result = $this->db->query($query);
						if ($result->num_rows() > 0)
						{
	   						$row = $result->row();
							$id = $row->id;
						}
						else
						{
							$this->Log('SQL ERROR in tourdates.php line 246');
							die();
						}
						$query="INSERT INTO `dates` (
									`tour_id`,
									`type` ,
									`date` ,
									`price_id`
									)
									VALUES (
									'$tour_id',
									'$hotelType',
									'$aDates[$i]',
									'$id');";
						$this->db->query($query);
					}
				}
				$data=null;
			}
		}
	}
	function addAllMonth($year,$month,$tourid,$DBL,$TRPL,$SNGL,$CHLD,$HB,$d,$hotelType)
	{
		$m=$month;
		$y=$year;
		$n=($m!=2?(($m%2)^($m>7))+30:(!($y%400)||!($y%4)&&($y%25)?29:28));

		$this->load->database();
		//date('w', mktime(0, 0, 0,  $month,$i, $year));
		$this->log($n." - DAYS");
		for($i=1;$i<=$n;$i++)
		{
			$date=$year."-".$month."-".$i;
			$this->log(" Di=".$d[date('w', mktime(0, 0, 0,  $month,$i, $year))]." i=".$i."  N=".date('w', mktime(0, 0, 0,  $month,$i, $year)));
			if($d[date('w', mktime(0, 0, 0,  $month,$i, $year))]=="true")
			{
				$query="Select id, price_id from dates where date='".$date."'";
				$result=$this->db->query($query);
				foreach($result->result() as $row)
				{
					$query="delete from prices where id=".$row->price_id;
					$this->db->query($query);
					$query="delete from dates where id=".$row->id;
					$this->db->query($query);
				}
				$query= "INSERT INTO  `prices` (
												`DBL` ,
												`SNGL` ,
												`TRPL` ,
												`CHLD` ,
												`HB`
												)
												VALUES (
	  											'$DBL',
												'$SNGL',
												'$TRPL',
												'$CHLD',
												'$HB'
												);";
				$this->db->query($query);
				$query="Select id from prices order by id desc";
				$result = $this->db->query($query);
				if ($result->num_rows() > 0)
				{
	   				$row = $result->row();
					$id = $row->id;
				}
				else
				{
					$this->Log('SQL ERROR in tourdates.php line 246');
					die();
				}
				$query="INSERT INTO `dates` (
									`tour_id`,
									`type` ,
									`date` ,
									`price_id`
									)
									VALUES (
									'$tourid',
									'$hotelType',
									'$date',
									'$id');";
				$this->db->query($query);

			}

		}

	}
}